<?php
error_reporting(E_ALL ^ E_NOTICE);

set_time_limit(0);
date_default_timezone_set("PRC"); //timezone

$db=mysql_connect("localhost","root","root"); 
mysql_select_db('faithwar',$db); 
 
$logdate =  mysql_query("SELECT DISTINCT logdate FROM `report_nojailbreak`");
//$dateTime = date("Y-m-d",strtotime("-1 day"));

    //$deviceid = mysql_fetch_array($deviceid_query);
    //while($deviceid[deviceid])

while($everylogdate = mysql_fetch_array($logdate))
{
 //sevenTotal 七日前的一天新注册用户(7天前某一天)
  //var_dump($everylogdate[logdate]);
    $sevenTotal_query = mysql_query("SELECT DISTINCT deviceid
        FROM faithwar_sevenday
        WHERE firstlogin = '$everylogdate[logdate]' and date='$everylogdate[logdate]' ");
    //$sevenTotal = mysql_fetch_array($sevenTotal_query); //list
    $sevenTotalRow = mysql_num_rows($sevenTotal_query);//总行
    
        //firstLoss 第一日 交集
    $firstLoss_query = mysql_query("SELECT count(DISTINCT a.deviceid)
        FROM (
        SELECT DISTINCT a.deviceid
        FROM faithwar_sevenday  a
        WHERE   a.firstlogin ='$everylogdate[logdate]' and a.date='$everylogdate[logdate]'
        )a
        INNER JOIN (

        SELECT DISTINCT deviceid
        FROM faithwar_sevenday 
        WHERE  date = DATE_SUB( '$everylogdate[logdate]', INTERVAL -1 DAY )
        )b ON a.deviceid = b.deviceid ");// 差集
    $firstLoss = mysql_fetch_array($firstLoss_query);
    //firstWastageRate 七日流失率 第一天
    $firstWastageRate_query = mysql_query("SELECT concat(round( '$firstLoss[0]'/ '$sevenTotalRow' *100,2), '%' )");
    $firstWastageRate = mysql_fetch_array($firstWastageRate_query);


        //secondLoss 第二日 交集
    $secondLoss_query = mysql_query("SELECT count(DISTINCT a.deviceid)
        FROM (
        SELECT DISTINCT a.deviceid
        FROM faithwar_sevenday  a
        WHERE  a.firstlogin ='$everylogdate[logdate]' and a.date='$everylogdate[logdate]'
        )a
        INNER JOIN (

        SELECT DISTINCT deviceid
        FROM faithwar_sevenday 
        WHERE  date = DATE_SUB( '$everylogdate[logdate]', INTERVAL -2 DAY )
        )b ON a.deviceid = b.deviceid ");//
    $secondLoss = mysql_fetch_array($secondLoss_query);
    //secondWastageRate 七日流失率 第二天
    $secondWastageRate_query = mysql_query("SELECT concat(round( '$secondLoss[0]'/ '$sevenTotalRow' *100,2), '%' )");
    $secondWastageRate = mysql_fetch_array($secondWastageRate_query);
        //thirdLoss 第三日 交集
    $thirdLoss_query = mysql_query("SELECT count(DISTINCT a.deviceid)
        FROM (
        SELECT DISTINCT a.deviceid
        FROM faithwar_sevenday  a
        WHERE  a.firstlogin ='$everylogdate[logdate]' and a.date='$everylogdate[logdate]'
        )a
        INNER JOIN (

        SELECT DISTINCT deviceid
        FROM faithwar_sevenday 
        WHERE  date = DATE_SUB( '$everylogdate[logdate]', INTERVAL -3 DAY ) 
        )b ON a.deviceid = b.deviceid ");
    $thirdLoss = mysql_fetch_array($thirdLoss_query);
    //thirdWastageRate 七日流失率 第三天
    $thirdWastageRate_query = mysql_query("SELECT concat(round( '$thirdLoss[0]'/ '$sevenTotalRow' *100,2), '%' )");
    $thirdWastageRate = mysql_fetch_array($thirdWastageRate_query);
        //fourthLoss 第四日 交集
    $fourthLoss_query = mysql_query("SELECT count(DISTINCT a.deviceid)
        FROM (
        SELECT DISTINCT a.deviceid
        FROM faithwar_sevenday  a
        WHERE  a.firstlogin ='$everylogdate[logdate]' and a.date='$everylogdate[logdate]'
        )a
        INNER JOIN (

        SELECT DISTINCT deviceid
        FROM faithwar_sevenday 
        WHERE  date = DATE_SUB( '$everylogdate[logdate]', INTERVAL -4 DAY ) 
        )b ON a.deviceid = b.deviceid ");
    $fourthLoss = mysql_fetch_array($fourthLoss_query);
    //fourthWastageRate 七日流失率 第四天
    $fourthWastageRate_query = mysql_query("SELECT concat(round( '$fourthLoss[0]'/ '$sevenTotalRow' *100,2), '%' )");
    $fourthWastageRate = mysql_fetch_array($fourthWastageRate_query);
        //fifthLoss 第五日 交集
    $fifthLoss_query = mysql_query("SELECT count(DISTINCT a.deviceid)
        FROM (
        SELECT DISTINCT a.deviceid
        FROM faithwar_sevenday  a
        WHERE   a.firstlogin ='$everylogdate[logdate]' and a.date='$everylogdate[logdate]'
        )a
        INNER JOIN (

        SELECT DISTINCT deviceid
        FROM faithwar_sevenday 
        WHERE  date = DATE_SUB( '$everylogdate[logdate]', INTERVAL -5 DAY ) 
        )b ON a.deviceid = b.deviceid ");
    $fifthLoss = mysql_fetch_array($fifthLoss_query);

    //fifthWastageRate 七日流失率 第五天
    $fifthWastageRate_query = mysql_query("SELECT concat(round( '$fifthLoss[0]'/ '$sevenTotalRow' *100,2), '%' )");
    $fifthWastageRate = mysql_fetch_array($fifthWastageRate_query);
   
    //sixthLoss 第六日 交集
    $sixthLoss_query = mysql_query("SELECT count(DISTINCT a.deviceid)
        FROM (
        SELECT DISTINCT a.deviceid
        FROM faithwar_sevenday  a
        WHERE   a.firstlogin ='$everylogdate[logdate]' and a.date='$everylogdate[logdate]'
        )a
        INNER JOIN (

        SELECT DISTINCT deviceid
        FROM faithwar_sevenday 
        WHERE  date = DATE_SUB( '$everylogdate[logdate]', INTERVAL -6 DAY ) 
        )b ON a.deviceid = b.deviceid ");
    $sixthLoss = mysql_fetch_array($sixthLoss_query);
    //sixthWastageRate 七日流失率 第六天
    $sixthWastageRate_query = mysql_query("SELECT concat(round( '$sixthLoss[0]'/ '$sevenTotalRow' *100,2), '%' )");
    $sixthWastageRate = mysql_fetch_array($sixthWastageRate_query);
   
    //seventhLoss 第七日 交集
    $seventhLoss_query = mysql_query("SELECT count(DISTINCT a.deviceid)
        FROM (
        SELECT DISTINCT a.deviceid
        FROM faithwar_sevenday  a
        WHERE   a.firstlogin ='$everylogdate[logdate]' and a.date='$everylogdate[logdate]'
        )a
        INNER JOIN (

        SELECT DISTINCT deviceid
        FROM faithwar_sevenday 
        WHERE  date = DATE_SUB( '$everylogdate[logdate]', INTERVAL -7 DAY ) 
        )b ON a.deviceid = b.deviceid ");
    $seventhLoss = mysql_fetch_array($seventhLoss_query);

    //seventhWastageRate 七日流失率 第七天
    $seventhWastageRate_query = mysql_query("SELECT concat(round( '$seventhLoss[0]'/ '$sevenTotalRow' *100,2), '%' )");
    $seventhWastageRate = mysql_fetch_array($seventhWastageRate_query);

    $sql = "UPDATE report_nojailbreak SET firstWastageRate= '$firstWastageRate[0]',secondWastageRate='$secondWastageRate[0]',thirdWastageRate='$thirdWastageRate[0]',fourthWastageRate='$fourthWastageRate[0]',fifthWastageRate='$fifthWastageRate[0]',sixthWastageRate='$sixthWastageRate[0]',seventhWastageRate='$seventhWastageRate[0]' WHERE logdate= '$everylogdate[logdate]'";
if(mysql_query($sql,$db)){
    echo "1 update is ok!";
} else {
    echo "failed£º".mysql_error();
}
  if ($everylogdate[logdate] ='2012-09-11') {
    //sevenTotal 七日总用户(7天前某一天)
  //var_dump($everylogdate[logdate]);
    $sevenTotal_query = mysql_query("SELECT DISTINCT deviceid
        FROM faithwar_sevenday
        WHERE firstlogin = '$everylogdate[logdate]' and date='$everylogdate[logdate]'");
    //$sevenTotal = mysql_fetch_array($sevenTotal_query); //list
    $sevenTotalRow = mysql_num_rows($sevenTotal_query);//总行
    
        //firstLoss 第一日 交集
    $firstLoss_query = mysql_query("SELECT count(DISTINCT a.deviceid)
        FROM (
        SELECT DISTINCT a.deviceid
        FROM faithwar_sevenday  a
        WHERE  a.firstlogin =  '$everylogdate[logdate]' and a.date='$everylogdate[logdate]'
        )a
        INNER JOIN (

        SELECT DISTINCT deviceid
        FROM faithwar_sevenday 
        WHERE  date = DATE_SUB( '$everylogdate[logdate]', INTERVAL -1 DAY )
        )b ON a.deviceid = b.deviceid  ");// 差集
    $firstLoss = mysql_fetch_array($firstLoss_query);
    //firstWastageRate 七日流失率 第一天 -- 修改为留存率
    $firstWastageRate_query = mysql_query("SELECT concat(round( '$firstLoss[0]'/ '$sevenTotalRow' *100,2), '%' )");
    $firstWastageRate = mysql_fetch_array($firstWastageRate_query);


        //secondLoss 第二日 交集
    $secondLoss_query = mysql_query("SELECT count(DISTINCT a.deviceid)
        FROM (
        SELECT DISTINCT a.deviceid
        FROM faithwar_sevenday  a
        WHERE  a.firstlogin =  '$everylogdate[logdate]' and a.date='$everylogdate[logdate]'
        )a
        INNER JOIN (

        SELECT DISTINCT deviceid
        FROM faithwar_sevenday 
        WHERE  date = DATE_SUB( '$everylogdate[logdate]', INTERVAL -2 DAY )
        )b ON a.deviceid = b.deviceid ");//
    $secondLoss = mysql_fetch_array($secondLoss_query);
    //secondWastageRate 七日流失率 第二天
    $secondWastageRate_query = mysql_query("SELECT concat(round( '$secondLoss[0]'/ '$sevenTotalRow' *100,2), '%' )");
    $secondWastageRate = mysql_fetch_array($secondWastageRate_query);
        //thirdLoss 第三日 交集
    $thirdLoss_query = mysql_query("SELECT count(DISTINCT a.deviceid)
        FROM (
        SELECT DISTINCT a.deviceid
        FROM faithwar_sevenday  a
        WHERE  a.firstlogin =  '$everylogdate[logdate]' and a.date='$everylogdate[logdate]'
        )a
        INNER JOIN (

        SELECT DISTINCT deviceid
        FROM faithwar_sevenday 
        WHERE  date = DATE_SUB( '$everylogdate[logdate]', INTERVAL -3 DAY ) 
        )b ON a.deviceid = b.deviceid ");
    $thirdLoss = mysql_fetch_array($thirdLoss_query);
    //thirdWastageRate 七日流失率 第三天
    $thirdWastageRate_query = mysql_query("SELECT concat(round( '$thirdLoss[0]'/ '$sevenTotalRow' *100,2), '%' )");
    $thirdWastageRate = mysql_fetch_array($thirdWastageRate_query);
        //fourthLoss 第四日 交集
    $fourthLoss_query = mysql_query("SELECT count(DISTINCT a.deviceid)
        FROM (
        SELECT DISTINCT a.deviceid
        FROM faithwar_sevenday  a
        WHERE  a.firstlogin =  '$everylogdate[logdate]' and a.date='$everylogdate[logdate]'
        )a
        INNER JOIN (

        SELECT DISTINCT deviceid
        FROM faithwar_sevenday 
        WHERE  date = DATE_SUB( '$everylogdate[logdate]', INTERVAL -4 DAY ) 
        )b ON a.deviceid = b.deviceid ");
    $fourthLoss = mysql_fetch_array($fourthLoss_query);
    //fourthWastageRate 七日流失率 第四天  
    $fourthWastageRate_query = mysql_query("SELECT concat(round( '$fourthLoss[0]'/ '$sevenTotalRow' *100,2), '%' )");
    $fourthWastageRate = mysql_fetch_array($fourthWastageRate_query);
        //fifthLoss 第五日 交集
    $fifthLoss_query = mysql_query("SELECT count(DISTINCT a.deviceid)
        FROM (
        SELECT DISTINCT a.deviceid
        FROM faithwar_sevenday  a
        WHERE  a.firstlogin =  '$everylogdate[logdate]' and a.date='$everylogdate[logdate]'
        )a
        INNER JOIN (

        SELECT DISTINCT deviceid
        FROM faithwar_sevenday 
        WHERE  date = DATE_SUB( '$everylogdate[logdate]', INTERVAL -5 DAY ) 
        )b ON a.deviceid = b.deviceid ");
    $fifthLoss = mysql_fetch_array($fifthLoss_query);

    //fifthWastageRate 七日流失率 第五天
    $fifthWastageRate_query = mysql_query("SELECT concat(round( '$fifthLoss[0]'/ '$sevenTotalRow' *100,2), '%' )");
    $fifthWastageRate = mysql_fetch_array($fifthWastageRate_query);
   
    //sixthLoss 第六日 交集
    $sixthLoss_query = mysql_query("SELECT count(DISTINCT a.deviceid)
        FROM (
        SELECT DISTINCT a.deviceid
        FROM faithwar_sevenday  a
        WHERE  a.firstlogin =  '$everylogdate[logdate]' and a.date='$everylogdate[logdate]'
        )a
        INNER JOIN (

        SELECT DISTINCT deviceid
        FROM faithwar_sevenday 
        WHERE  date = DATE_SUB( '$everylogdate[logdate]', INTERVAL -6 DAY ) 
        )b ON a.deviceid = b.deviceid ");
    $sixthLoss = mysql_fetch_array($sixthLoss_query);
    //sixthWastageRate 七日流失率 第六天
    $sixthWastageRate_query = mysql_query("SELECT concat(round( '$sixthLoss[0]'/ '$sevenTotalRow' *100,2), '%' )");
    $sixthWastageRate = mysql_fetch_array($sixthWastageRate_query);
   
    //seventhLoss 第七日 交集
    $seventhLoss_query = mysql_query("SELECT count(DISTINCT a.deviceid)
        FROM (
        SELECT DISTINCT a.deviceid
        FROM faithwar_sevenday  a
        WHERE  a.firstlogin =  '$everylogdate[logdate]' and a.date='$everylogdate[logdate]'
        )a
        INNER JOIN (

        SELECT DISTINCT deviceid
        FROM faithwar_sevenday 
        WHERE  date = DATE_SUB( '$everylogdate[logdate]', INTERVAL -7 DAY ) 
        )b ON a.deviceid = b.deviceid ");
    $seventhLoss = mysql_fetch_array($seventhLoss_query);

    //seventhWastageRate 七日流失率 第七天
    $seventhWastageRate_query = mysql_query("SELECT concat(round('$seventhLoss[0]'/ '$sevenTotalRow' *100,2), '%' )");
    $seventhWastageRate = mysql_fetch_array($seventhWastageRate_query);

    $sql2 = "UPDATE report_nojailbreak SET firstWastageRate= '$firstWastageRate[0]',secondWastageRate='$secondWastageRate[0]',thirdWastageRate='$thirdWastageRate[0]',fourthWastageRate='$fourthWastageRate[0]',fifthWastageRate='$fifthWastageRate[0]',sixthWastageRate='$sixthWastageRate[0]',seventhWastageRate='$seventhWastageRate[0]' WHERE logdate= '$everylogdate[logdate]'";
if(mysql_query($sql2,$db)){
    echo "1 update is ok!";
} else {
    echo "failed£º".mysql_error();
}
  }

  
}


//mysql_close($con);
?>